﻿Imports Tohto.Base.CommonConst
Imports Tohto.Base.ComUtility
Imports FarPoint.Web
Imports AjaxControlToolkit
Imports Tohto.Web.BLL
Imports FarPoint.Web.Spread
Imports System.Drawing


Public Class KR0050P01
    Inherits CMBasePage

#Region "定数"

    ''' <summary>
    ''' 画面名称
    ''' </summary>
    ''' <remarks></remarks>
    Private Const PAGE_NM As String = "敷金精算確定一覧.csv"

    ''' <summary>
    ''' 戻る 
    ''' </summary>
    ''' <remarks></remarks>
    Public goBack As String = String.Empty

    ''' <summary>
    ''' 画面ID
    ''' </summary>
    ''' <remarks></remarks>
    Private Const PAGE_ID As String = "KR0050P01"

    ''' <summary>
    ''' 敷金精算確定一覧テーブル
    ''' </summary>
    ''' <remarks></remarks>
    Private Const SIKINGAKUDEI_TABLE As String = "_SIKINGAKUDEI_TABLE"

#End Region

#Region "フォームロードイベント"

    ''' <summary>
    ''' フォームロード 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'ページ名称をセット 
        DirectCast(Me.Master, MainMaster).pageName = PAGE_NM

        Try

            'エラーメッセージリストの初期化 
            Call ErrMessageInit()

            If Not IsPostBack Then

                '画面の初期化 
                Call PageInit()

                If MyBase.isGoBack Then
                    '遷移先から戻る場合 
                    Call GoBackInputInit()

                    '伝票一覧を呼び出し 
                    Call GetSelectData()

                End If

            End If

        Catch ex As Exception
            MyBase.ProcessException(ex, ComConst.EX_HANLDER_UI)
        End Try
    End Sub

#End Region

#Region "ボタンクリックイベント"

    ''' <summary>
    ''' 検索ボタンを押下する場合 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub btn_search_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_search.Click
        Try

            Call MyBase.ErrMsgAreaClear()

            '一覧データ取得を呼び出し、結果を取得する。 
            Call GetSelectData()

            '画面を遷移する、ページの値をテーブルに保存する　
            Dim gamenValue As DataTable = SaveFormValue()
            Session.Add(ComConst.GAMEN_ID.SIKINGAKUDEI_ITIRAN & SIKINGAKUDEI_TABLE, gamenValue)

        Catch ex As Exception
            MyBase.ProcessException(ex, ComConst.EX_HANLDER_UI)
        End Try
    End Sub

    ''' <summary>
    ''' クリアボタンを押下する場合 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub but_clear_Click(ByVal sender As Object, ByVal e As EventArgs) Handles but_clear.Click
        Try

            '画面のクリア
            Call PageClear()

        Catch ex As Exception
            MyBase.ProcessException(ex, ComConst.EX_HANLDER_UI)
        End Try
    End Sub

    ''' <summary>
    ''' CSVボタンを押下する場合 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub btn_csv_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_csv.Click
        Try

            Call MyBase.ErrMsgAreaClear()

            'CSVファイルに出力する。

            Dim scmMain As ToolkitScriptManager = DirectCast(DirectCast(Me.Master, MainMaster).FindControl("scmMain"), ToolkitScriptManager)
            Dim needCsv As HiddenField = DirectCast(DirectCast(Me.Master, MainMaster).FindControl("hidNeedCsv"), HiddenField)
            Call scmMain.RegisterDataItem(needCsv, "true")

            Session.Add(ComConst.SEE_CSV_SPREADVIEW, Me.sp_sikingakudei.ActiveSheetView)
            Session.Add(ComConst.SEE_CSV_FILENM, PAGE_NM)

        Catch ex As Exception
            MyBase.ProcessException(ex, ComConst.EX_HANLDER_UI)
        End Try
    End Sub


#End Region

#Region "スプレッドクリックイベント"
    ''' <summary>
    ''' 契約者名を押下する場合 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub sp_sikingakudei_ButtonCommand(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles sp_sikingakudei.ButtonCommand
        Try
            Dim intRow As Integer = DirectCast(e.CommandArgument, System.Drawing.Point).X
            Dim intCol As Integer = DirectCast(e.CommandArgument, System.Drawing.Point).Y

            If intCol = 1 Then
                Dim seisanno As String = CStr(Me.sp_sikingakudei.Cells(intRow, 0).Value)

                Response.Redirect(ComConst.GAMEN_ID.SEISAN_NYURYOKU & ".aspx?" & _
                                  ComConst.GAMEN_MODE & "=" & Server.UrlDecode(ComConst.MODE_EDITABLE) & "&" & _
                                  ComConst.QUERY_SEISAN_NO & "=" & Server.UrlDecode(seisanno), False)
            End If
        Catch ex As Exception
            MyBase.ProcessException(ex, ComConst.EX_HANLDER_UI)
        End Try
    End Sub
#End Region

#Region "ソメッド"

    ''' <summary>
    ''' エラーメッセージリストの初期化 
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ErrMessageInit()

        'クライアントにポープメッセージ共通をレジスト 
        MyBase.RegistErrorCdToForm()
    End Sub

    ''' <summary>
    ''' 画面の初期化 
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub PageInit()

        '共通ボタンのリセット
        SetComBtnState(False, False, False, False, False)

        '端末情報の制御
        Call MyBase.SetTanmatuState(False, False)

        '戻る

        Call SetGoBackUrl(Request, PAGE_ID)

        '画面クリア
        Call PageClear()

        'コンボをセット
        Call SetCmbBox()

        '日付フォーカスイベントをセット
        MyBase.RegistHidukeScript(txt_ym, True)     '精算日
        'スプレッドＣＳＳ
        SetSpreadStyle(Me.sp_sikingakudei, True)

    End Sub

    ''' <summary>
    ''' 画面のクリア
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub PageClear()

        'エラーメッセージ領域のクリア
        Call MyBase.ErrMsgAreaClear()

        Me.cmb_busyo.SelectedValue = MyBase.sessionUserInfo.POST_CD                         '部署 
        Me.txt_ym.Text = MyBase.GetSystemDate.ToShortDateString                             '精算日 
        Me.sp_sikingakudei.ActiveSheetView.RowCount = 0                                     '敷金精算 
        Me.txt_ym.Focus()                                                                   '精算日 
    End Sub

    ''' <summary>
    ''' コンボボックスをセット
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetCmbBox()
        'コンボボックス初期化 
        Call InitDropDownList(ComConst.CMB_BUSYO, cmb_busyo)
    End Sub

    ''' <summary>
    ''' 一覧データ取得を呼び出し、結果を取得する。 
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub GetSelectData()
        '一覧データを取得します。 
        Dim dtKojiTyeka As DataTable = GetDisplayData()
        Dim iRowIndex As Integer = 0
        Dim blnValue As Boolean
        Dim strOld As String
        Dim str_New As String
        Dim decSeisan_Kingaku As Decimal = 0
        Dim decSeisan_Kingaku_Totle As Decimal = 0
        Dim borderColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.BORDERCOLOR)

        'スプレッドに値を与える
        If Not dtKojiTyeka Is Nothing Then
            'スプレッドＣＳＳ
            SetSpreadStyle(Me.sp_sikingakudei, True)
            sp_sikingakudei.Columns.Count = 16
            sp_sikingakudei.Rows.Count = 0
            For i As Integer = 0 To dtKojiTyeka.Rows.Count - 1
                With dtKojiTyeka.Rows(i)
                    str_New = Util.ObjToString(.Item("BANK_BRANCH_CD"))
                    If i = 0 Then
                        blnValue = True
                        strOld = str_New
                    End If

                    sp_sikingakudei.Rows.Add(sp_sikingakudei.Rows.Count, 3)
                    CreatKaishuSPD(sp_sikingakudei, sp_sikingakudei.Rows.Count - 3, blnValue)

                    If blnValue = True Then
                        blnValue = False
                    Else
                        blnValue = True
                    End If
                    iRowIndex = sp_sikingakudei.Rows.Count - 3
                    'スプレッド
                    sp_sikingakudei.ColumnHeader.DefaultStyle.Border.BorderColor = borderColor
                    If sp_sikingakudei.ColumnFooter.Columns.Count > 0 Then
                        For index As Integer = 0 To sp_sikingakudei.ActiveSheetView.Columns.Count - 1
                            sp_sikingakudei.Cells(iRowIndex, index).Border.BorderSizeTop = BorderStyle.Solid
                            sp_sikingakudei.Cells(iRowIndex, index).Border.BorderSizeTop = 1
                            sp_sikingakudei.Cells(iRowIndex, index).Border.BorderSizeRight = BorderStyle.Solid
                            sp_sikingakudei.Cells(iRowIndex, index).Border.BorderSizeRight = 1
                            sp_sikingakudei.Cells(iRowIndex, index).Border.BorderSizeLeft = -1
                            sp_sikingakudei.Cells(iRowIndex, index).Border.BorderSizeBottom = 0
                        Next
                    End If

                    Dim spButtonLink As New Spread.ButtonCellType
                    spButtonLink.ButtonType = Spread.ButtonType.LinkButton
                 

                    Me.sp_sikingakudei.ActiveSheetView.Cells(iRowIndex, 0).Value = .Item("SEISAN_NO")  '非表示項目（退室精算No.）
                    Me.sp_sikingakudei.Cells(iRowIndex + 2, 0).Value = .Item("HIYO_FUTAN_KB")        '非表示項目（費用負担区分）

                    '契約者名
                    spButtonLink.Text = Util.ObjToString(.Item("CONTRA_CUST_NM"))
                    Me.sp_sikingakudei.ActiveSheetView.Cells(iRowIndex, 1).CellType = spButtonLink
                    Me.sp_sikingakudei.ActiveSheetView.Cells(iRowIndex, 1).Value = Util.ObjToString(.Item("CONTRA_CUST_NM"))


                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 1).Value = .Item("CONTRA_CUST_CD")       '契約者CD
                    Me.sp_sikingakudei.Cells(iRowIndex + 2, 1).Value = .Item("HIYO_FUTAN_NM")        '費用負担区分名

                    Me.sp_sikingakudei.Cells(iRowIndex, 2).Value = .Item("ARTICLE_NM")               '物件名
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 2).Value = .Item("ROOM_NM")              '部屋名
                    Me.sp_sikingakudei.Cells(iRowIndex + 2, 2).Value = .Item("ARTICLE_ROOM_CD")      '費用負担区分名

                    Me.sp_sikingakudei.Cells(iRowIndex, 3).Value = .Item("SIKIKIN_KINGAKU")          '敷金 
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 3).Value = .Item("KOJI_KINGAKU")         '工事代

                    Me.sp_sikingakudei.Cells(iRowIndex, 4).Value = .Item("RETURN_PAY")               '戻し賃料
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 4).Value = .Item("MINO_PAY")             '未納賃料

                    Me.sp_sikingakudei.Cells(iRowIndex, 5).Value = .Item("TOSPAL_KINGAKU")           'トスパル
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 5).Value = .Item("S_SHOKYAKU_KINGAKU")   '敷金償却

                    Me.sp_sikingakudei.Cells(iRowIndex, 6).Value = .Item("NYUKYOSHAKAI_KINGAKU")     '入居者会 
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 6).Value = .Item("H_SHOKYAKU_KINGAKU")   '保証金償却 

                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 7).Value = .Item("TESURYO_KB")           '非表示項目（振込手数料区分）
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 8).Value = .Item("TESURYO_KBNM")         '振込手数料
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 9).Value = .Item("SEISAN_KINGAKU")       '精算金額

                    decSeisan_Kingaku = decSeisan_Kingaku + Util.ObjToDecimal(.Item("SEISAN_KINGAKU"))
                    decSeisan_Kingaku_Totle = decSeisan_Kingaku_Totle + Util.ObjToDecimal(.Item("SEISAN_KINGAKU"))

                    Me.sp_sikingakudei.Cells(iRowIndex, 10).Value = .Item("SEISAN_YT_DT")             '精算日
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 10).Value = .Item("KAKUNIN_DT")           '担当確認日
                    Me.sp_sikingakudei.Cells(iRowIndex + 2, 10).Value = .Item("JOCHO_DT")             '上長確認日

                    Me.sp_sikingakudei.Cells(iRowIndex, 11).Value = .Item("BANK_BRANCH_CD")           '銀行CD-支店CD
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 11).Value = .Item("BANK_NM")              '銀行名
                    Me.sp_sikingakudei.Cells(iRowIndex + 2, 11).Value = .Item("BRANCH_NM")            '支店名

                    Me.sp_sikingakudei.Cells(iRowIndex, 12).Value = .Item("KOZA_SHUBETU")            '非表示項目（口座種別CD）

                    Me.sp_sikingakudei.Cells(iRowIndex, 13).Value = .Item("KOZA_SHUBETU_NM")         '口座種目
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 13).Value = .Item("KOZA_NO")             '口座番号
                    Me.sp_sikingakudei.Cells(iRowIndex + 2, 13).Value = .Item("KOZA_KNM")            '口座名義

                    Me.sp_sikingakudei.Cells(iRowIndex, 14).Value = .Item("SEISAN_POST_CD")         '非表示項目（精算担当部署CD）
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 14).Value = .Item("SEISAN_EMP_CD")      '非表示項目（精算担当者CD）

                    Me.sp_sikingakudei.Cells(iRowIndex, 15).Value = .Item("SEISAN_POST_NM")          '精算担当部署
                    Me.sp_sikingakudei.Cells(iRowIndex + 1, 15).Value = .Item("SEISAN_EMP_NM")       '精算担当者名


                    If i <> dtKojiTyeka.Rows.Count - 1 Then
                        If str_New <> Util.ObjToString(dtKojiTyeka.Rows(i + 1)("BANK_BRANCH_CD")) Then
                            sp_sikingakudei.Rows.Add(sp_sikingakudei.Rows.Count, 1)
                            CreatKaishuSPD(sp_sikingakudei, sp_sikingakudei.Rows.Count - 1, True, True)
                            iRowIndex = sp_sikingakudei.Rows.Count - 1
                            Me.sp_sikingakudei.Cells(iRowIndex, 8).Value = "小計"         '振込手数料

                            Me.sp_sikingakudei.Cells(iRowIndex, 9).Value = decSeisan_Kingaku       '精算金額
                            decSeisan_Kingaku = 0
                            blnValue = True
                            strOld = Util.ObjToString(dtKojiTyeka.Rows(i + 1)("BANK_BRANCH_CD"))
                        End If
                    Else
                        sp_sikingakudei.Rows.Add(sp_sikingakudei.Rows.Count, 1)
                        CreatKaishuSPD(sp_sikingakudei, sp_sikingakudei.Rows.Count - 1, True, True)
                        iRowIndex = sp_sikingakudei.Rows.Count - 1
                        Me.sp_sikingakudei.Cells(iRowIndex, 8).Value = "小計"         '振込手数料 
                        Me.sp_sikingakudei.Cells(iRowIndex, 9).Value = decSeisan_Kingaku       '精算金額 

                        sp_sikingakudei.Rows.Add(sp_sikingakudei.Rows.Count, 1)
                        CreatKaishuSPD(sp_sikingakudei, sp_sikingakudei.Rows.Count - 1, True, True)
                        iRowIndex = sp_sikingakudei.Rows.Count - 1
                        Me.sp_sikingakudei.Cells(iRowIndex, 8).Value = "合計"         '振込手数料

                        Me.sp_sikingakudei.Cells(iRowIndex, 9).Value = decSeisan_Kingaku_Totle       '精算金額

                        Me.sp_sikingakudei.ActiveSheetView.SelectionModel.AddSelection(0, 0, 3, 12)

                        For iCol As Integer = 0 To 15
                            sp_sikingakudei.Cells(iRowIndex, iCol).Border.BorderColor = borderColor
                            sp_sikingakudei.Cells(iRowIndex, iCol).Border.BorderSizeLeft = 0
                            sp_sikingakudei.Cells(iRowIndex, iCol).Border.BorderSizeRight = 1
                            sp_sikingakudei.Cells(iRowIndex, iCol).Border.BorderSizeBottom = 1
                            sp_sikingakudei.Cells(iRowIndex, iCol).Border.BorderSizeTop = 1
                        Next

                        sp_sikingakudei.RowHeader.Cells(iRowIndex, 0).Border.BorderColor = borderColor
                        sp_sikingakudei.RowHeader.Cells(iRowIndex, 0).Border.BorderSizeLeft = 0
                        sp_sikingakudei.RowHeader.Cells(iRowIndex, 0).Border.BorderSizeRight = 1
                        sp_sikingakudei.RowHeader.Cells(iRowIndex, 0).Border.BorderSizeBottom = 0
                        sp_sikingakudei.RowHeader.Cells(iRowIndex, 0).Border.BorderSizeTop = 1
                    End If

                End With
            Next
        End If
    End Sub

    ''' <summary>
    ''' スプレッドのStyle
    ''' </summary>
    ''' <param name="objSpread"></param>
    ''' <param name="blnRowOkiColor">１行おきの背景色</param>
    ''' <remarks></remarks>
    Private Sub SetSpreadStyle(ByRef objSpread As FpSpread, Optional ByVal blnRowOkiColor As Boolean = False, Optional ByVal needSort As Boolean = True, Optional ByVal isInputSp As Boolean = False)
        Dim styleInfo As New Spread.StyleInfo

        Dim backcolor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.BACKCOLOR)
        Dim foreColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.FORECOLOR)
        Dim selbackColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.SELBACKCOLOR)
        Dim borderColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.BORDERCOLOR)
        Dim evenbackColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.SP_EVENROW_BACKCOLOR)
        Dim oddbackColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.SP_ODDROW_BACKCOLOR)

        styleInfo.BackColor = backcolor
        styleInfo.ForeColor = foreColor
        styleInfo.Background.BackgroundImageUrl = ""
        styleInfo.Background.SelectedBackgroundImageUrl = ""
        styleInfo.Border.BorderColor = borderColor
        styleInfo.Border.BorderSizeRight = 1
        styleInfo.Border.BorderSizeLeft = 0
        styleInfo.Border.BorderSizeBottom = 0
        styleInfo.Border.BorderSizeTop = 0


        styleInfo.HorizontalAlign = HorizontalAlign.Center
        styleInfo.VerticalAlign = VerticalAlign.Middle
        objSpread.RowHeader.DefaultStyle = styleInfo
        objSpread.ColumnHeader.DefaultStyle = styleInfo

        Dim styleInfoConer As New Spread.StyleInfo
        styleInfoConer.BackColor = backcolor
        styleInfoConer.ForeColor = foreColor
        styleInfoConer.Background.BackgroundImageUrl = ""
        styleInfoConer.Background.SelectedBackgroundImageUrl = ""
        styleInfoConer.Border.BorderColor = borderColor
        styleInfoConer.HorizontalAlign = HorizontalAlign.Center
        styleInfoConer.VerticalAlign = VerticalAlign.Middle
        styleInfoConer.Border.BorderSizeRight = 1
        styleInfoConer.Border.BorderStyleRight = BorderStyle.Solid
        objSpread.SheetCorner.DefaultStyle = styleInfoConer


        Dim sv As Spread.SheetView = objSpread.ActiveSheetView
        sv.SelectionBackColor = selbackColor
        sv.SelectionForeColor = Color.SeaGreen
        sv.HeaderGrayAreaColor = backcolor

        '１行おきの背景色をセット
        If blnRowOkiColor Then
            sv.AlternatingRows(0).BackColor = oddbackColor
            sv.AlternatingRows(1).BackColor = evenbackColor
        End If

        'スプレッドのCSSを設定します    
        objSpread.ActiveSheetView.DefaultStyle.Margin.Left = 1
        objSpread.ActiveSheetView.DefaultStyle.Margin.Right = 1
        objSpread.CssClass = "spreadforall"

        If needSort = True Then
            objSpread.ActiveSheetView.AllowSort = True
        End If

        objSpread.ActiveSheetView.DefaultStyle.Border.BorderColor = Color.Black

        If isInputSp = True Then
            objSpread.ActiveSheetView.OperationMode = OperationMode.Normal
            objSpread.ActiveSheetView.SelectionPolicy = Model.SelectionPolicy.Single
            objSpread.AllowHeaderResize = False
            objSpread.ActiveSheetView.Columns.Default.Resizable = False
            objSpread.ActiveSheetView.Rows.Default.Resizable = False
        Else
            objSpread.ActiveSheetView.OperationMode = OperationMode.SingleSelect
            objSpread.AllowHeaderResize = False
            objSpread.ActiveSheetView.Columns.Default.Resizable = True
            objSpread.ActiveSheetView.Rows.Default.Resizable = False
            objSpread.ActiveSheetView.SelectionPolicy = Model.SelectionPolicy.MultiRange
        End If

        objSpread.ActiveSheetView.Columns(0).Visible = False
        objSpread.ActiveSheetView.Columns(7).Visible = False
        objSpread.ActiveSheetView.Columns(12).Visible = False
        objSpread.ActiveSheetView.Columns(14).Visible = False
    End Sub

    Private Sub GoBackInputInit()

        Dim dt As DataTable = CType(Session(ComConst.GAMEN_ID.SIKINGAKUDEI_ITIRAN & SIKINGAKUDEI_TABLE), DataTable)

        With dt.Rows(0)

            Me.cmb_busyo.SelectedValue = Util.ObjToString(.Item("POST_CD"))                        '部署

            If .Item("DT_SEISAN_DT") Is DBNull.Value Then
                Me.txt_ym.Text = ""      '精算日
            Else
                Me.txt_ym.Text = Util.ObjToString(CDate(.Item("DT_SEISAN_DT")).ToShortDateString)      '精算日
            End If


        End With
    End Sub

    ''' <summary>
    ''' 保存画面の値
    ''' </summary>
    ''' <remarks></remarks>
    Private Function SaveFormValue() As DataTable

        Dim dt As New DataTable

        dt.Columns.Add("POST_CD", Type.GetType("System.String"))                   '部署

        dt.Columns.Add("DT_SEISAN_DT", Type.GetType("System.DateTime"))            '精算日

        Dim dr As DataRow = dt.NewRow
        dr("POST_CD") = Me.cmb_busyo.SelectedValue
        If txt_ym.Text = "" Then
            dr("DT_SEISAN_DT") = DBNull.Value
        Else
            dr("DT_SEISAN_DT") = Me.txt_ym.Text
        End If

        dt.Rows.Add(dr)

        Return dt

    End Function

    ''' <summary>
    ''' 一覧データテーブル
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDisplayData() As DataTable

        Dim dt As New DataTable("KAKUTEI_ICHIRAN")

        Dim kr0050B As New KR0050P01B01
        Dim ds As DataSet = kr0050B.Fetch_LIST(MyBase.sessionUserInfo.EMPLOYEE_CD, _
                                               Me.cmb_busyo.SelectedValue, _
                                               Me.txt_ym.Text, _
                                               errInfo)

        Dim dtKAKUTEI As DataTable = ds.Tables("KAKUTEI_ICHIRAN")

        If dtKAKUTEI.Rows.Count = 0 Then
            sp_sikingakudei.ActiveSheetView.RowCount = 0
            Call MyBase.SetMsgArea("CMI0003")

            '年月をフォーカスにする()
            Me.txt_ym.Focus()
            Return Nothing
        Else
            Return dtKAKUTEI
        End If

    End Function

    ''' <summary>
    ''' 一覧スプレッドを作成
    ''' </summary>
    ''' <param name="spdReset"></param>
    ''' <param name="iIndex"></param>
    ''' <remarks></remarks>
    Private Sub CreatKaishuSPD(ByVal spdReset As FpSpread, ByVal iIndex As Integer, ByVal blnBackColor As Boolean, Optional ByVal blnSumFlag As Boolean = False)

        spdReset.SaveChanges()
        Dim spLabel As Spread.LabelCellType = Nothing
        Dim spDateTime As Spread.DateTimeCellType = Nothing
        Dim spInteger As Spread.IntegerCellType = Nothing
        Dim spNumberFormat As New System.Globalization.NumberFormatInfo
        Dim rowcount As Integer = 0
        Dim borderColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.BORDERCOLOR)

        spNumberFormat.NumberDecimalDigits = 3
        spNumberFormat.NumberDecimalSeparator = ","


        Dim evenbackColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.SP_EVENROW_BACKCOLOR)
        Dim oddbackColor As Color = ColorTranslator.FromHtml(ComConst.SPREAD_COLOR.SP_ODDROW_BACKCOLOR)

        If Not blnSumFlag Then
            If blnBackColor Then
                Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex).BackColor = oddbackColor
                Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex + 1).BackColor = oddbackColor
                Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex + 2).BackColor = oddbackColor
            Else
                Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex).BackColor = evenbackColor
                Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex + 1).BackColor = evenbackColor
                Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex + 2).BackColor = evenbackColor
            End If

        Else
            Me.sp_sikingakudei.ActiveSheetView.Rows(iIndex).BackColor = oddbackColor
        End If


        With spdReset
            'セルのレイアウトの設定

            .RowHeader.Visible = True
            If Not blnSumFlag Then
                .RowHeader.Cells(iIndex, 0).RowSpan = 3  'Rowヘッダフォーマット 
            Else
                .RowHeader.Cells(iIndex, 0).RowSpan = 1  'Rowヘッダフォーマット 
            End If

            .Cells(iIndex, 0).RowSpan = 1
            .Cells(iIndex, 1).RowSpan = 1
            .Cells(iIndex, 2).RowSpan = 1
            .Cells(iIndex, 3).RowSpan = 1
            .Cells(iIndex, 4).RowSpan = 1
            .Cells(iIndex, 5).RowSpan = 1
            .Cells(iIndex, 6).RowSpan = 1
            .Cells(iIndex, 7).RowSpan = 1
            .Cells(iIndex, 8).RowSpan = 1
            .Cells(iIndex, 9).RowSpan = 1
            .Cells(iIndex, 10).RowSpan = 1
            .Cells(iIndex, 11).RowSpan = 1
            .Columns(0).Visible = False
            .Columns(7).Visible = False
            .Columns(12).Visible = False
            .Columns(14).Visible = False

            If Not blnSumFlag Then
                spLabel = New Spread.LabelCellType
                .Cells(iIndex, 1).CellType = spLabel
                .Cells(iIndex, 1).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex, 1).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 1, 1).CellType = spLabel
                .Cells(iIndex + 1, 1).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 1).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 2, 1).CellType = spLabel
                .Cells(iIndex + 2, 1).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 2, 1).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex, 2).CellType = spLabel
                .Cells(iIndex, 2).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex, 2).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 1, 2).CellType = spLabel
                .Cells(iIndex + 1, 2).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 2).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 2, 2).CellType = spLabel
                .Cells(iIndex + 2, 2).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 2, 2).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex, 3).CellType = spInteger
                .Cells(iIndex, 3).Locked = True
                .Cells(iIndex, 3).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex, 3).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex + 1, 3).CellType = spInteger
                .Cells(iIndex + 1, 3).Locked = True
                .Cells(iIndex + 1, 3).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex + 1, 3).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex, 4).CellType = spInteger
                .Cells(iIndex, 4).Locked = True
                .Cells(iIndex, 4).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex, 4).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex + 1, 4).CellType = spInteger
                .Cells(iIndex + 1, 4).Locked = True
                .Cells(iIndex + 1, 4).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex + 1, 4).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex, 5).CellType = spInteger
                .Cells(iIndex, 5).Locked = True
                .Cells(iIndex, 5).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex, 5).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex + 1, 5).CellType = spInteger
                .Cells(iIndex + 1, 5).Locked = True
                .Cells(iIndex + 1, 5).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex + 1, 5).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex, 6).CellType = spInteger
                .Cells(iIndex, 6).Locked = True
                .Cells(iIndex, 6).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex, 6).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex + 1, 6).CellType = spInteger
                .Cells(iIndex + 1, 6).Locked = True
                .Cells(iIndex + 1, 6).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex + 1, 6).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 1, 8).CellType = spLabel
                .Cells(iIndex + 1, 8).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 8).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex + 1, 9).CellType = spInteger
                .Cells(iIndex + 1, 9).Locked = True
                .Cells(iIndex + 1, 9).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex + 1, 9).VerticalAlign = VerticalAlign.Middle

                spDateTime = New Spread.DateTimeCellType
                .Cells(iIndex, 10).CellType = spDateTime
                .Cells(iIndex, 10).Locked = True
                .Cells(iIndex, 10).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex, 10).VerticalAlign = VerticalAlign.Middle

                spDateTime = New Spread.DateTimeCellType
                .Cells(iIndex + 1, 10).CellType = spDateTime
                .Cells(iIndex + 1, 10).Locked = True
                .Cells(iIndex + 1, 10).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 10).VerticalAlign = VerticalAlign.Middle

                spDateTime = New Spread.DateTimeCellType
                .Cells(iIndex + 2, 10).CellType = spDateTime
                .Cells(iIndex + 2, 10).Locked = True
                .Cells(iIndex + 2, 10).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 2, 10).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex, 12).CellType = spLabel
                .Cells(iIndex, 12).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex, 12).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 1, 12).CellType = spLabel
                .Cells(iIndex + 1, 12).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 12).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 2, 12).CellType = spLabel
                .Cells(iIndex + 2, 12).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 2, 12).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex, 13).CellType = spLabel
                .Cells(iIndex, 13).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex, 13).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 1, 13).CellType = spLabel
                .Cells(iIndex + 1, 13).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 13).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 2, 13).CellType = spLabel
                .Cells(iIndex + 2, 13).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 2, 13).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex, 15).CellType = spLabel
                .Cells(iIndex, 15).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex, 15).VerticalAlign = VerticalAlign.Middle

                spLabel = New Spread.LabelCellType
                .Cells(iIndex + 1, 15).CellType = spLabel
                .Cells(iIndex + 1, 15).HorizontalAlign = HorizontalAlign.Left
                .Cells(iIndex + 1, 15).VerticalAlign = VerticalAlign.Middle


                For i As Integer = 0 To 15
                    If iIndex <> 0 Then
                        .Cells(iIndex, i).Border.BorderColor = borderColor
                        .Cells(iIndex, i).Border.BorderSizeLeft = 0
                        .Cells(iIndex, i).Border.BorderSizeRight = 1
                        .Cells(iIndex, i).Border.BorderSizeBottom = 0
                        .Cells(iIndex, i).Border.BorderSizeTop = 1

                        .RowHeader.Cells(iIndex, 0).Border.BorderColor = borderColor
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeLeft = 0
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeRight = 1
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeBottom = 0
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeTop = 1
                    Else
                        .Cells(iIndex, i).Border.BorderColor = borderColor
                        .Cells(iIndex, i).Border.BorderSizeLeft = 0
                        .Cells(iIndex, i).Border.BorderSizeRight = 1
                        .Cells(iIndex, i).Border.BorderSizeBottom = 0
                        .Cells(iIndex, i).Border.BorderSizeTop = 0

                        .RowHeader.Cells(iIndex, 0).Border.BorderColor = borderColor
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeLeft = 0
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeRight = 1
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeBottom = 0
                        .RowHeader.Cells(iIndex, 0).Border.BorderSizeTop = 0
                    End If

                    .Cells(iIndex + 1, i).Border.BorderColor = borderColor
                    .Cells(iIndex + 1, i).Border.BorderSizeLeft = 0
                    .Cells(iIndex + 1, i).Border.BorderSizeRight = 1
                    .Cells(iIndex + 1, i).Border.BorderSizeBottom = 0
                    .Cells(iIndex + 1, i).Border.BorderSizeTop = 0

                    .Cells(iIndex + 2, i).Border.BorderColor = borderColor
                    .Cells(iIndex + 2, i).Border.BorderSizeLeft = 0
                    .Cells(iIndex + 2, i).Border.BorderSizeRight = 1
                    .Cells(iIndex + 2, i).Border.BorderSizeBottom = 0
                    .Cells(iIndex + 2, i).Border.BorderSizeTop = 0
                Next

            Else
                spLabel = New Spread.LabelCellType
                .Cells(iIndex, 8).CellType = spLabel
                .Cells(iIndex, 8).HorizontalAlign = HorizontalAlign.Center
                .Cells(iIndex, 8).VerticalAlign = VerticalAlign.Middle

                spInteger = New Spread.IntegerCellType
                spInteger.NumberFormat = spNumberFormat
                .Cells(iIndex, 9).CellType = spInteger
                .Cells(iIndex, 9).Locked = True
                .Cells(iIndex, 9).HorizontalAlign = HorizontalAlign.Right
                .Cells(iIndex, 9).VerticalAlign = VerticalAlign.Middle

                For i As Integer = 0 To 15
                    .Cells(iIndex, i).Border.BorderColor = borderColor
                    .Cells(iIndex, i).Border.BorderSizeLeft = 0
                    .Cells(iIndex, i).Border.BorderSizeRight = 1
                    .Cells(iIndex, i).Border.BorderSizeBottom = 0
                    .Cells(iIndex, i).Border.BorderSizeTop = 1
                Next

                .RowHeader.Cells(iIndex, 0).Border.BorderColor = borderColor
                .RowHeader.Cells(iIndex, 0).Border.BorderSizeLeft = 0
                .RowHeader.Cells(iIndex, 0).Border.BorderSizeRight = 1
                .RowHeader.Cells(iIndex, 0).Border.BorderSizeBottom = 0
                .RowHeader.Cells(iIndex, 0).Border.BorderSizeTop = 1
            End If

        End With

    End Sub

#End Region

End Class